package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.xmy.cultivate.entity.SchoolNeeds;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TeacherPoint;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 校区招聘需求表 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-12-11
 */
public interface SchoolNeedsMapper extends BaseMapper<SchoolNeeds> {




    @Select({
            "<script>",
            "WITH with_tb01 AS (",
            "    SELECT",
            "        t1.id,",
            "        t1.status,",
            "        t1.school_id,",
            "        t1.station_id,",
            "        t1.subjects_id,",
            "        t1.department_id,",
            "        t1.need_count,",
            "        t3.name AS department_name,",
            "        CONCAT(t2.name, IFNULL(t4.name, '')) AS station_needs_name,",
            "        (CASE WHEN t2.type = 1 THEN t1.school_id ELSE t1.department_id END) AS school_or_department_id,",
            "        (CASE WHEN t2.type = 1 THEN t5.name ELSE t3.name END) AS school_name",
            "    FROM",
            "        adms_school_needs AS t1",
            "        INNER JOIN adms_station AS t2 ON t1.station_id = t2.id",
            "        LEFT JOIN department AS t3 ON t1.department_id = t3.id",
            "        LEFT JOIN subjects AS t4 ON t1.subjects_id = t4.id",
            "        LEFT JOIN organization AS t5 ON t1.school_id = t5.id",
            "    WHERE 1=1",
            "   <if test='status != null and status !=&apos;&apos;'>",
            "       AND t1.status  = ${status}",
            "   </if>",
            " ),",
            "with_tb02 AS (",
            "    SELECT",
            "        t1.try_date,",
            "        t1.pass_type,",
            "        t1.try_audit_date,",
            "        t2.name,",
            "        t2.phone,",
            "        t2.inviter_id,",
            "        t3.station_id,",
            "        t3.subjects_id,",
            "        t3.department_id,",
            "        t1.school_id,",
            "        (CASE WHEN t3.type = 1 THEN t1.school_id ELSE t3.department_id END) AS school_or_department_id,",
            "        (CASE WHEN t3.type = 1 THEN t4.name ELSE t5.name END) AS school_name,",
            "        CASE",
            "            WHEN EXISTS (SELECT 1 FROM role_admin WHERE admin_id = t2.inviter_id AND role_id = 1568160915052400644) THEN 1",
            "            ELSE 2",
            "        END AS role_id",
            "    FROM",
            "        adms_try_job AS t1",
            "        INNER JOIN adms_recruit AS t2 ON t2.id = t1.recruit_id",
            "        INNER JOIN adms_station_needs AS t3 ON t1.station_needs_id = t3.id",
            "        INNER JOIN organization AS t4 ON t1.school_id = t4.id",
            "        LEFT JOIN department AS t5 ON t3.department_id = t5.id",
            "    WHERE",
            "        t1.is_arrive_job = 0",
            " )",
            "SELECT",
            "id,",
            "`status`,",
            "    school_or_department_id,",
            "    school_name,",
            "    station_needs_name,",
            "    need_count,",
            "    tryJobPassCount,",
            "    tryJobIngCount,",
            "    (need_count - tryJobRecruitCount) AS needRecruitment,",
            "     station_id,",
            "     subjects_id",
            "FROM",
            "    (",
            "        SELECT",
            "            SUM(need_count) AS need_count,",
            "            SUM(tryJobPassCount) AS tryJobPassCount,",
            "            SUM(tryJobIngCount) AS tryJobIngCount,",
            "            SUM(tryJobRecruitCount) AS tryJobRecruitCount,",
            "id,",
            "`status`,",
            "            school_or_department_id,",
            "            school_name,",
            "            station_needs_name,",
            "            station_id,",
            "            subjects_id",
            "        FROM",
            "            (",
            "                SELECT",
            "                    need_count,",
            "                    SUM(CASE WHEN pass_type = 1 THEN 1 ELSE 0 END) AS tryJobPassCount,",
            "                    SUM(CASE WHEN pass_type = 0 THEN 1 ELSE 0 END) AS tryJobIngCount,",
            "                    SUM(CASE WHEN pass_type IN (0, 1) THEN 1 ELSE 0 END) AS tryJobRecruitCount,",
            "                    with_tb01.id,",
            "                    with_tb01.status,",
            "                    with_tb01.school_or_department_id,",
            "                    with_tb01.school_name,",
            "                    with_tb01.station_id,",
            "                    with_tb01.subjects_id,",
            "                    with_tb01.station_needs_name",
            "                FROM",
            "                    with_tb01",
            "                    LEFT JOIN with_tb02 AS t01 ON t01.school_or_department_id = with_tb01.school_or_department_id",
            "                        AND t01.subjects_id = with_tb01.subjects_id",
            "                        AND t01.station_id = with_tb01.station_id",
            "                GROUP BY",
            "                    with_tb01.school_or_department_id,",
            "                    with_tb01.station_id,",
            "                    with_tb01.subjects_id",
            "            ) AS t",
            "        GROUP BY",
            "            school_or_department_id,",
            "            station_id,",
            "            subjects_id",
            "    ) AS tt",
            "WHERE 1=1",
            "   <if test='schoolOrDepartmentId != null and schoolOrDepartmentId !=&apos;&apos;'>",
            "       AND school_or_department_id = ${schoolOrDepartmentId}",
            "   </if>",
            "   <if test='stationId != null and stationId !=&apos;&apos;'>",
            "       AND station_id = ${stationId}",
            "   </if>",
            "order by id desc",
            "</script>"
    })
    public  IPage< SchoolNeeds> findAll(
            IPage<SchoolNeeds> page,
            @Param("schoolOrDepartmentId") String schoolOrDepartmentId,
            @Param("stationId") String stationId,
            @Param("status") String status

    );


    @Update("update adms_school_needs set school_id = #{schoolId},department_id = #{departmentId},station_id = #{stationId},subjects_id = #{subjectsId},status =#{status}  where id = #{id}")
    boolean updateSchoolNeedsInfo(@Param("schoolId") Long schoolId,
                                   @Param("departmentId") Long departmentId,
                                   @Param("stationId") Long stationId,
                                   @Param("subjectsId") Long subjectsId,
                                   @Param("id") Long id,
                                  @Param("status") Integer status);

}
